package com.huayi.website.repository;

import com.huayi.website.entity.EnumLanguage;
import com.huayi.website.entity.PageContentEntity;
import com.huayi.website.entity.PhotoAttachmentEntity;
import com.huayi.website.entity.PhotoEntity;
import com.huayi.website.utils.PagingResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class PageContentRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public List<PageContentEntity> getAllList(Integer pageId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS p.* FROM").append(Tables.PageContent).append(" as p ");
        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(pageId!=null && pageId>0) {
            sb.append(" AND page_id=? ");
            params.add(pageId);
        }
        sb.append(" ORDER BY p.id DESC ");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(PageContentEntity.class), params.toArray(new Object[params.size()]));
    }

    @Transactional
    public PageContentEntity getById(Integer pageContentId) {
        try {
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT SQL_CALC_FOUND_ROWS p.* FROM").append(Tables.PageContent).append(" as p ");
            sb.append(" where id=? ");
            var pageContent = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(PageContentEntity.class), pageContentId);
            return pageContent;
        } catch (Exception e) {
            return null;
        }
    }

    public PageContentEntity getByPageIdAndLanguage(Integer pageId, EnumLanguage language) {
        try {
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT SQL_CALC_FOUND_ROWS p.* FROM").append(Tables.PageContent).append(" as p ");
            sb.append(" where page_id=? AND language=? ORDER BY p.sort desc LIMIT 1");
            var pageContent = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(PageContentEntity.class), pageId,language.getIndex());
            return pageContent;
        } catch (Exception e) {
            return new PageContentEntity();
        }
    }

    public Integer editPageContent(Integer pageContentId,String title,String image,String image1,String content,Integer sort,Integer pageId,Integer language){
        if(pageContentId == null || pageContentId<=0){
            //添加
            String sql ="INSERT INTO "+Tables.PageContent+" (title,image,image1,content,sort,page_id,language) VALUE (?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, title);
                    ps.setString(2, image);
                    ps.setString(3, image1);
                    ps.setString(4, content);
                    ps.setInt(5, sort);
                    ps.setInt(6, pageId);
                    ps.setInt(7, language);

                    return ps;
                }
            }, keyHolder);

            Integer id = keyHolder.getKey().intValue();
            return id;
        }else{
            String sql = "UPDATE "+Tables.PageContent+" SET title=?,image=?,image1=?,content=?,sort=?,page_id=?,language=? WHERE id=?";
            jdbcTemplate.update(sql,title,image,image1,content,sort,pageId,language,pageContentId);
            return pageContentId;
        }
    }

    public void delete(Integer pageContentId){
        jdbcTemplate.update("DELETE FROM "+Tables.PageContent+ " WHERE id=?",pageContentId);
    }
}
